#!/usr/bin/env python
# -*- coding:utf-8 -*-

# file:do_sql1.py
# author: 马国鑫
# datetime:2022/4/19 22:31
# software: PyCharm

"""
this is function  description 
"""

# import module your need

import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer, Enum, Date, ForeignKey  # 导入外键
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationships

HOST = '127.0.0.1'
USERNAME = 'root'
PASSWORD = '021207'
DBNAME = 'python_db'
PORT = 3306
engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format(USERNAME, PASSWORD, HOST, PORT, DBNAME),
                       echo=True)
print(engine)
Base = declarative_base()


class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer, primary_key=True, autoincrement=True)  # 字段名一致 不重复写
    # 主键和自增
    name = Column(String(64), nullable=False)
    age = Column(Integer)

    def __repr__(self):
        return "<{} id = {}, name = {},age = {}>".format(
            __class__.__name__, self.id, self.name, self.age)


Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
from sqlalchemy.orm import Session

session: Session = sessionmaker(bind=engine)()
student = Student(name='tom')
student.name = 'mgx'
student.age = 19
session.add(student)
session.commit()

student = session.query(Student).get(1)
print(student)
student.name = 'xm'
student.age = 20
print(student)
session.add(student)
session.commit()

student2 = Student(name='xiaoming')
student2.age = 14
session.add(student2)
session.commit()
print(student2)

# 先查询在删除
student2 = session.query(Student).get(2)
print(student2)
session.delete(student2)
session.commit()



